import 'package:path/path.dart';
import 'package:sqflite_common/sqlite_api.dart';
import 'package:sqflite_common_ffi/sqflite_ffi.dart';
import 'package:intl/intl.dart';

// unit - 时间单位：0=日，1=月，2=年
Future<Database> getDatabase() async {
  //sqfliteFfiInit();
  var databaseFactory = createDatabaseFactoryFfi();

  var p = await databaseFactory.getDatabasesPath();
  var pdb = join(p, 'data.db');
  print(pdb);

  var db = await databaseFactory.openDatabase(pdb);

  await db.execute('''CREATE TABLE IF NOT EXISTS todo(
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      title TEXT,
      start DATE
    )''');
  await db.execute('''CREATE TABLE IF NOT EXISTS loops(
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      todoid INTEGER,
      unit INTEGER,
      num INTEGER
    )''');

  return db;
}

mixin databaseFactoryFfi {}

Future<void> initDb(Database db) async {
  var sqls = [
    'DELETE FROM todo',
    'DELETE FROM loops',
    'INSERT INTO todo(id,title,start) values (1,"work1","2023-10-21")',
    'INSERT INTO todo(id,title,start) values (2,"work2","2023-10-22")',
    'INSERT INTO todo(id,title,start) values (3,"work3","2023-10-23")',
    'INSERT INTO todo(id,title,start) values (4,"work4","2023-10-24")',
    'INSERT INTO todo(title,start) values ("work5","2023-10-25")',
    'INSERT INTO loops(todoid,unit,num) values (1,0,7)',
    'INSERT INTO loops(todoid,unit,num) values (2,1,4)',
    'INSERT INTO loops(todoid,unit,num) values (3,2,1)',
  ];
  sqls.forEach((element) async {
    await db.execute(element);
  });
}

Future<void> generateNextRecord(Database db, int id) async {
  const sql1 =
      'SELECT todo.title,todo.start,loops.unit,loops.num from todo left join loops on todo.id=loops.todoid where todo.id=?';
  var res = await db.rawQuery(sql1, [
    id,
  ]);
  if (res.isEmpty) {
    return;
  }
  if (res[0]['num'] == null) {
    //print('${res[0]} not loop');
    //res[0].forEach((k, v) {
    //print('$k => $v');
    //});
    return;
  }
  String title = res[0]['title'] as String;
  DateTime start = DateTime.parse(res[0]['start'] as String);
  int unit = res[0]['unit'] as int;
  int num = res[0]['num'] as int;
  DateTime? startNew;
  var formater = DateFormat('yyyy-MM-dd');
  switch (unit) {
    case 0: //day
      startNew = start.add(Duration(days: num));
    //print(formater.format(startNew));
    case 1: //month
      startNew =
          DateTime.parse('${start.year}-${start.month + num}-${start.day}');
    //print('${start.year}-${start.month + num}-${start.day}');
    case 2: //year
      startNew =
          DateTime.parse('${start.year + num}-${start.month}-${start.day}');
    //print(formater.format(startNew));
  }

  // insert
  const sql2 = 'INSERT INTO todo(title,start) values (?,?)';
  if (startNew != null) {
    await db.rawInsert(sql2, [title, formater.format(startNew)]);
  }
}
